<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/TemplateAD.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>CircleU - Administrator</title>
<?php
 	session_start(); 
	if( !$_SESSION["login"] || !isset($_SESSION["AdminID"]))header('Location: error_page.php');
	include("db_util.php");
	putenv("ORACLE_HOME=/usr/local/libexec/oracle/app/oracle/product/11.2.0/client_1");
	$connection=DbConnect();
	if(!$connection)
		echo "DisConnected!<br>";
	$query='SELECT * FROM Administrator where AID=' . $_SESSION["AdminID"]; 
	$statement = oci_parse($connection, $query);
	oci_execute($statement);
	$admin = oci_fetch_array($statement, OCI_ASSOC+OCI_RETURN_NULLS);
	oci_free_statement($statement);
	
	// Yan Qiao
	$n_results = -1;  // number of query results
	
	$check_OK = true;
	$error_msg = "";
	
	$item_deleted = false;
	$msg = "";
	if(isset($_POST["s_search"])){
		$from = " student s ";
		$where = "";
		if($_POST["s_id"]!=""){ // unique constraint, 1 or zero student can be fournd
			if(is_numeric($_POST["s_id"]))
				$where=$where . " and SID=" . $_POST["s_id"];
			else{
				$check_OK = false;
				$error_msg = "Please enter an valid number.";
			}
		}
		if($_POST["s_email"]!=""){
			$where=$where . " and Email='" . $_POST["s_email"] . "'";
		}
		if($_POST["s_fname"]!=""){
			$where=$where . " and FIRST_NAME like '%" . $_POST["s_fname"] . "%'";
		}
		if($_POST["s_lname"]!=""){
			$where=$where . " and LAST_NAME like '%" . $_POST["s_lname"] . "%'";
		}
		if($_POST["s_department"]!=""){
			$where=$where . " and DEPARTMENT='" . $_POST["s_department"] . "'";
		}
		if($_POST["s_hobby"]!=""){
			$where=$where . " and HOBBIES LIKE '%" . $_POST["s_hobby"] . "%'";
		}
		if($_POST["s_course"]!=""){			
			$where= $where . " and exists(select * from StuCourse sc, Course c where STUDENTID=s.SID and sc.CourseID=c.CID and " .
				   (is_numeric($_POST["s_course"])? 
				     ("sc.CourseID=" . $_POST["s_course"]):("c.CNAME like '%" . $_POST["s_course"] . "%'")) . ")";
		}
		if($_POST["s_group"]!=""){
			$where= $where . " and exists(select * from StuGroup sg, Groups g where STUDENTID=s.SID and sg.GroupID=g.GID and ".
				   (is_numeric($_POST["s_group"])?
				     ("sg.GroupID=" . $_POST["s_group"]):("g.NAME like '%" . $_POST["s_group"] . "%'")) . ")";
		}
		if($where!=""){  // get rid of the first " and"
			$where=substr($where, 4);
			$query = "select * from " . $from . " where " . $where;
		}else{
			$query = "select * from " . $from;
		}
		//echo "Query: ". $query;
		if($check_OK){
			$stmt = oci_parse($connection, $query);
			if(!$stmt){
				$check_OK = false;
				$error_msg = "Internal error.";
			}else{
				oci_execute($stmt);
				$n_results = oci_fetch_all($stmt, $s_result, null, null, OCI_FETCHSTATEMENT_BY_ROW);
				oci_free_statement($stmt);
			}
		}
	}//done student search
	elseif(isset($_POST["g_search"])){
		$from = " Groups g, (select GID, count(StudentID) as N_MEMBERS from 
				StuGroup right outer join Groups on GroupID=GID group by GID ";  // make sure the group with no member will showup
		if($_POST["g_min"]!="" || $_POST["g_max"]!=""){
			if($_POST["g_min"]!="" && !is_numeric($_POST["g_min"])){
				$check_OK=false;
				$error_msg = "Please enter a valid number.";
			}elseif($_POST["g_max"]!="" && !is_numeric($_POST["g_max"])){
				$check_OK=false;
				$error_msg = "Please enter a valid number.";
			}else{
				if($_POST["g_min"]!="" && $_POST["g_max"]!=""){
					$from = $from . "having count(StudentID)>=" . $_POST["g_min"] . " and count(StudentID)<=" . $_POST["g_max"];
				}elseif($_POST["g_max"]!=""){
					$from = $from . "having count(StudentID)<=" . $_POST["g_max"];
				}elseif($_POST["g_min"]!=""){
					$from = $from . "having count(StudentID)>=" . $_POST["g_min"];
				}
			}
		}
		$from = $from . ") gs";
		$where = " g.GID=gs.GID ";
		if($_POST["g_id"]!=""){
			if(is_numeric($_POST["g_id"]))
				$where=$where .  " and g.GID=".$_POST["g_id"];
			else{
				$check_OK = false;
				$error_msg = "Please enter an valid number.";
			}
		}
		if($_POST["g_name"]!=""){
			$where = $where . " and Name like '%" . $_POST["g_name"] . "%'";
		}
		$query = "select g.GID, NAME, DESCRIPTION, N_MEMBERS from " . $from . " where " . $where;
		if($check_OK){
			$stmt = oci_parse($connection, $query);
			if(!$stmt){
				$check_OK = false;
				$error_msg = "Internal error.";
			}else{
				oci_execute($stmt);
				$n_results = oci_fetch_all($stmt, $g_result, null, null, OCI_FETCHSTATEMENT_BY_ROW);
				oci_free_statement($stmt);
			}
		}
	}// done group
	elseif(isset($_POST["c_search"])){
		$from = " Course c, (select CID, count(StudentID) as N_MEMBERS from 
				StuCourse right outer join Course on CourseID=CID group by CID ";// make sure course with no member will showup
		if($_POST["c_min"]!="" || $_POST["c_max"]!=""){
			if($_POST["c_min"]!="" && !is_numeric($_POST["c_min"])){
				$check_OK=false;
				$error_msg = "Please enter a valid number.";
			}elseif($_POST["c_max"]!="" && !is_numeric($_POST["c_max"])){
				$check_OK=false;
				$error_msg = "Please enter a valid number.";
			}else{
				if($_POST["c_min"]!="" && $_POST["c_max"]!=""){
					$from = $from . "having count(StudentID)>=" . $_POST["c_min"] . " and count(StudentID)<=" . $_POST["c_max"];
				}elseif($_POST["c_max"]!=""){
					$from = $from . "having count(StudentID)<=" . $_POST["c_max"];
				}elseif($_POST["c_min"]!=""){
					$from = $from . "having count(StudentID)>=" . $_POST["c_min"];
				}
			}
		}
		$from = $from . ") cs";
		$where = " c.CID=cs.CID ";
		if($_POST["c_id"]!=""){
			if(is_numeric($_POST["c_id"]))
				$where = $where . " and c.CID=" .$_POST["c_id"];
			else{
				$check_OK = false;
				$error_msg = "Please enter an valid number.";
			}
		}
		if($_POST["c_name"]!=""){
			$where = $where . " and CNAME like '%" . $_POST["c_name"] . "%'";
		}
		if($_POST["c_semester"]!="any"){
			$where = $where . " and Semester='" . $_POST["c_semester"] . "'";
		}
		if($_POST["c_instructor"]!=""){
			$where = $where . " and Instructor like '%" . $_POST["c_instructor"]. "%'";
		}
		$query = "select c.CID, CNAME, Instructor, Semester, N_MEMBERS from " . $from . " where " . $where;
		if($check_OK){
			$stmt = oci_parse($connection, $query);
			if(!$stmt){
				$check_OK = false;
				$error_msg = "Internal error.";
			}else{
				oci_execute($stmt);
				$n_results = oci_fetch_all($stmt, $c_result, null, null, OCI_FETCHSTATEMENT_BY_ROW);
				oci_free_statement($stmt);
			}
		}
	} // done course search
	elseif(isset($_POST["s_delete"])){  // student delete
		$s_id = $_POST["sid_to_delete"];
		// delete from TWITTERREPLY TWITTER
		// delete from GroupPost 
		
		// WallPost Photo 
		
		// CoursePost COURSEREPLY CourseReview BookShare 
		
		// ISFriend StuGroup StuCourse 
		// Album 
		// delete from STUDENT
		oci_close($connection);
		header('Location: AdminHome.php');
	}// done student delete
	elseif(isset($_POST["g_delete"])){
		oci_close($connection);
		header('Location: AdminHome.php');
		
	}// done group delete
	elseif(isset($_POST["c_delete"])){
		$c_id = $_POST["cid_to_delete"];
		$query = "delete from StuCourse where CourseID=" . $c_id;
		$query = "delete from Course where CID=" . $c_id;
		oci_close($connection);
		header('Location: AdminHome.php');
	}// done course delete
	
?>
<!-- InstanceEndEditable -->
<style type="text/css">
<!--

a:link   {
text-decoration:   none;
}
a:visited   {
text-decoration:   none;
}
a:hover   {
text-decoration:   underline;
} 
.try {
	background-color: #FFFFFF;
	position: relative;
	z-index: auto;
	visibility: visible;
	clip: rect(auto,auto,auto,auto);
	width: auto;
	height: 76px;
}
.copyright {
	position: absolute;
	left: 236px;
	top: 600px;
	font-size: large;
	height: 22px;
}
.LayerImg {
	left: auto;
}
.CircleU {
	font-family: "Comic Sans MS";
	font-size: xx-large;
	font-weight: bold;
	position: relative;
	left: 20px;
	top: auto;
	height: auto;
	width: auto;
	visibility: visible;
	background-position: left;
	color: #3333FF;
}
#Title1 {
	position:absolute;
	width:1096px;
	height:60px;
	z-index:2;
	left: 9px;
	top: 6px;
}



}

#Dwt01 {
	position:absolute;
	width:1092px;
	height:500px;
	right: 9px;
	top: 80px;
}
#LeftLayer {
	position:absolute;
	width:200px;
	height:500px;
	z-index:3;
	left: 11px;
	top: 77px;
	background-color: #FF6600;
}
#EditR1 {
	position:absolute;
	width:878px;
	height:500px;
	z-index:4;
	left: 221px;
	top: 77px;
}
.STYLE3 {color: #0000CC}
#Layer1 {
	position:absolute;
	width:181px;
	height:217px;
	z-index:1;
	left: 2px;
	top: 2px;
}
.STYLE4 {
	font-size: 18px;
	font-weight: bold;
}
-->
</style><script type="text/JavaScript">
<!--
function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_showHideLayers() { //v6.0
  var i,p,v,obj,args=MM_showHideLayers.arguments;
  for (i=0; i<(args.length-2); i+=3) if ((obj=MM_findObj(args[i]))!=null) { v=args[i+2];
    if (obj.style) { obj=obj.style; v=(v=='show')?'visible':(v=='hide')?'hidden':v; }
    obj.visibility=v; }
}
//-->
</script>
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>

<body>
<center>
  <div id="Title1">
    <table width="1092" height="59" bgcolor="#FF6600">
      <tr>
        <td rowspan="2" class="CircleU"><div align="left"><?php echo "<a href=\"index.php\">"; ?> <span class="STYLE3">CircleU</a></span></div></td>
        <td width="128"><?php echo "<a href=\"index.php\">"; ?> </a></td>
        <td width="111"><div align="right"><?php echo "<a href=\"SignOut.php\">"; ?>Sign Out </a></div></td>
        <td width="120"><div align="center"><?php echo "<a href=\"ContactUs.php\">"; ?>Contact Us</a></div></td>
      </tr>
      <tr>
        <td><form id="form2" name="form2" method="post" <?php echo "action=\"AdminAdvancedSearch.php\""; ?>>
          <label>
            <input type="submit" name="AdvancedSearch" value="Advanced Search" />
          </label>
        </form>
        </td>
        <td colspan="2"><form id="form1" name="form1" method="post" <?php echo "action=\"AdminSearchResult.php\""; ?>>
          <label>
            <input type="text" name="search_text" />
          </label>
          <label>
          <input type="submit" name="Search" value="Search" />
          </label>
        </form>        </td>
      </tr>
    </table>
  </div>
  <div class="copyright">
<p>Copyright @ 2011 CircleU built in University of Florida. All right reserved&#13;</p>

</div>
  <div id="LeftLayer">
    <div id="Layer1">
	 <h3>Welcome, <?php echo $admin["FIRST_NAME"];?>!</h3>
      <table width="180" height="73" border="1">
	    <tr>
		  <td colspan="2" align="center" valign="middle">
			  <?php echo "<img src=\"". $admin["PROFILE_PIC"] . "\" width=\"180\" height=\"120\" />";?>
		  </td>
		</tr>
        <tr>
          <td width="76" height="34">Admin ID: </td>
          <td width="105"><?php echo $_SESSION["AdminID"]; ?></td>
        </tr>
        <tr>
          <td height="31">Name: </td>
          <td><?php echo $admin["FIRST_NAME"] . " " . $admin["LAST_NAME"];?></td>
        </tr>
      </table>
	<?php 
		echo "<p><a href=\"AdminHome.php\">Main Menu</a></p>";
		oci_close($connection);
	?>
    </div>
    
  
  </div>
  <div id="EditR1"><!-- InstanceBeginEditable name="EditRegion3" -->
  	<script type="text/javascript">
		function show_item_only(layer_name){
			document.getElementById('LayerGroup').style.display='none';
			document.getElementById('LayerFriends').style.display='none';
			document.getElementById('LayerCourse').style.display='none';
			document.getElementById(layer_name).style.display='inline-table';
		}
	</script>
  <div id="Layer3">
  	  <h3 style="text-align:center"> Advanced Search </h3>
  	  <div id="Layer2">
        <table width="500" border="0">
          <tr>
            <td width="248"><div class="Char">
              <div align="right">Search  Type: </div>
            </div></td>
            <td width="236"><div align="left">
              <form id="form3" name="form3" method="post" action="">
                <label>
                  <select name="select" size="1" >
				    <option>--</option>
                    <option onclick="show_item_only('LayerCourse')">Coures</option >
                    <option onclick="show_item_only('LayerFriends')">Students</option>
                    <option onclick="show_item_only('LayerGroup')">Groups</option>
                  </select>
                  </label>
              </form>
            </div></td>
          </tr>
        </table>
      </div>
      <div id="LayerGroup" <?php if(!isset($_GET["group_search"])) echo "style=\"display:none\"";?>>
        <form id="form5" name="form5" method="post" action="AdminAdvancedSearch.php?group_search=true">
          <table width="500" border="0">
		    <tr>
              <td><div align="right"><span class="Char"> Group ID:</span></div></td>
              <td><input type="text" name="g_id" /></td>
            </tr>
            <tr>
              <td><div align="right"><span class="Char"> Group Name:</span></div></td>
              <td><input type="text" name="g_name" /></td>             
            </tr>
			<tr>
              <td><div align="right"><span class="Char"># of Members Between: </span></div></td>
              <td><input type="text" name="g_min" size="6" /> and <input type="text" name="g_max" size="6" /></td>
            </tr>			
            <tr>
              <td colspan="2"><div align="center">
                <input type="submit" name="g_search" value="Submit" />
                <input type="reset" name="Submit6" value="Clear" />
              </div></td>
            </tr>
          </table>
        </form>
		<hr />
      </div>
	  <div id="LayerFriends" <?php if(!isset($_GET["student_search"])) echo "style=\"display:none\"";?>>
        <form id="form9" name="form9" method="post" action="AdminAdvancedSearch.php?student_search=true">
          <table width="600" border="0">			
            <tr>
			 <td><div align="right"><span class="Char">Student's ID</span>: </div></td>
             <td><input type="text" name="s_id" /></td>
			 <td style="text-align:right"> Email: </td>
			 <td><input type="text" name="s_email" />  </td>
            </tr>
              <td><div align="right"><span class="Char">First Name</span>: </div></td>
              <td><input type="text" name="s_fname" /></td>
			  <td style="text-align:right"> Last Name:</td>
              <td><input type="text" name="s_lname" /></td>
            </tr>
            <tr>
              <td><div align="right"><span class="Char">Department:</span> </div></td>
              <td><input type="text" name="s_department" /></td> <?php // TODO: to selection ?>
              <td><div align="right"><span class="Char">Hobby:</span></div></td>
              <td> <input type="text" name="s_hobby" /></td>
            </tr>
            <tr>
              <td><div align="right"><span class="Char">Attended Course:</span></div></td>
              <td><input type="text" name="s_course" /> </td>
			  <td style="text-align:right"> Joined Group:</td>
			  <td><input type="text" name="s_group" /></td>
            </tr>
            <tr>
              <td colspan="4"><label>
                <div align="center">
                  <input type="submit" name="s_search" value="Submit" />
                  <input type="reset" name="s_reset" value="Clear" />
                </div>
              </label></td>
            </tr>
          </table>
        </form>
		<hr />
	  </div>
	  <div id="LayerCourse" <?php if(!isset($_GET["course_search"])) echo "style=\"display:none\"";?>>
	    <form id="form4" name="form4" method="post" action="AdminAdvancedSearch.php?course_search=true">
	      <table width="500" border="0">
            <tr>
              <td><div align="right"><span class="Char">Coures ID:</span> </div></td>
              <td><input type="text" name="c_id" /></td>
              <td>
                <div align="right"><span class="Char">Name:</span> </div></td>
                <td><input type="text" name="c_name" /></td>
            </tr>
            <tr>
              <td><div align="right"><span class="Char">Semester:</span></div></td>
              <td><select name="c_semester">
			  	  <option value="any" selected="selected">Any</option>
			  	  <option value="Fall11">Fall11</option>
 				  <option value="Spring12">Spring'12</option>
 				  <option value="Summer12">Summer'12</option>
  				  <option value="Fall12">Fall'12</option>
			  </select> 
			  </td>
              <td><div align="right"><span class="Char">Instructor:</span></div></td>
              <td><input type="text" name="c_instructor" /></td>
            </tr>
			<tr>
              <td colspan="4"><span class="Char"># of Students Between: </span><input type="text" name="c_min" size="6" /> and <input type="text" name="c_max" size="6" /></td>
            </tr>	
            <tr>
              <td colspan="4"><div align="center">
                <input type="submit" name="c_search" value="Submit" />
                <input type="reset" name="c_reset" value="Clear" />
              </div></td>
            </tr>
          </table>
        </form>
		<hr />
      </div>
    </div>
	<div id="Layer4">
		<?php
			if($n_results!=-1 && $check_OK)
				echo "<b><font color=\"green\">" . $n_results . " results found." . "</font></b>";
			elseif(!$check_OK){
				echo "<font color=red><b>". $error_msg . "</b></font>";
			}
			if($item_deleted)
				echo  "<b><font color=\"green\">" . $msg . "</font></b>";
		?>
	  <?php // NOTE: Deletion action is extremely unsafe... but whatever, it's just a DB project. ?>
	  <form id="form5" name="form5" method="post" action="AdminAdvancedSearch.php?action=delete">
	  <table width="850" border="1">
		<?php
		  if(isset($_POST["s_search"]) && $n_results>0){
		  	echo "<tr><td style=\"text-align:center\">ID</td>
				<td style=\"text-align:center\">Name</td>
				<td style=\"text-align:center\">Email</td>
				<td style=\"text-align:center\">Dept.</td>
				<td style=\"text-align:center\">Hobbies</td>
				<td style=\"text-align:center\">Last Login</td>
				<td style=\"text-align:center\">Manage</td>
				</tr>";
			  foreach($s_result as $item){
				echo "<tr><td style=\"text-align:center\">" . 
					 $item["SID"] . "</td><td style=\"text-align:center\">". 
					 $item["FIRST_NAME"] . " " . $item["LAST_NAME"]. "</td><td style=\"text-align:center\">". 
					 $item["EMAIL"] . "</td><td style=\"text-align:center\">". 
					 $item["DEPARTMENT"] . "</td><td style=\"text-align:center\">" . 
					 $item["HOBBIES"] . "</td><td style=\"text-align:center\">" . 
					 $item["LAST_LOGIN_TIME"] . "</td><td style=\"text-align:center\">" .
					 "<input type=\"submit\" name=\"s_delete\" value=\"Delete Student\" />" . 
					 "<input type=\"hidden\" name=\"sid_to_delete\" value=" . $item["SID"] . " /></td>" .
					 "</tr>";
			  }
		  } elseif(isset($_POST["g_search"]) && $n_results>0){
		  	echo "<tr><td style=\"text-align:center\">Group ID</td>
				<td style=\"text-align:center\">Name</td>
				<td style=\"text-align:center\">Description</td>
				<td style=\"text-align:center\">Members</td>
				<td style=\"text-align:center\">Manage</td>
				</tr>";
			  foreach($g_result as $item){
				echo "<tr><td style=\"text-align:center\">" . 
					 $item["GID"] . "</td><td style=\"text-align:center\">". 
					 $item["NAME"] . "</td><td style=\"text-align:center\" width=\"350\">". 
					 $item["DESCRIPTION"] . "</td><td style=\"text-align:center\">". 
					 $item["N_MEMBERS"] . "</td><td style=\"text-align:center\">" . 
					 "<input type=\"button\" name=\"g_edit\" value=\"Edit Group\" 
					 	onclick=\"location.href='AdminEditGroup.php?GID=". $item["GID"] . "'\" />&nbsp;" . 
					 "<input type=\"submit\" name=\"g_delete\" value=\"Delete Group\" />" . 
					 "<input type=\"hidden\" name=\"gid_to_delete\" value=" . $item["GID"] . " /></td>" .
					 "</tr>";
			  }
		  } elseif(isset($_POST["c_search"]) && $n_results>0){
		  	echo "<tr><td style=\"text-align:center\">Course ID</td>
				<td style=\"text-align:center\">Name</td>
				<td style=\"text-align:center\">Instructor</td>
				<td style=\"text-align:center\">Semester</td>
				<td style=\"text-align:center\"># of Students</td>
				<td style=\"text-align:center\">Manage</td>
				</tr>";
			  foreach($c_result as $item){
				echo "<tr><td style=\"text-align:center\">" . 
					 $item["CID"] . "</td><td style=\"text-align:center\">". 
					 $item["CNAME"] . "</td><td style=\"text-align:center\">". 
					 $item["INSTRUCTOR"] . "</td><td style=\"text-align:center\">". 
					 $item["SEMESTER"] . "</td><td style=\"text-align:center\">". 
					 $item["N_MEMBERS"] . "</td><td style=\"text-align:center\">" . 
					 "<input type=\"button\" name=\"c_edit\" value=\"Edit Course\" 
					 	onclick=\"location.href='AdminEditCourse.php?CID=". $item["CID"] . "'\" />&nbsp;" . 
					 "<input type=\"submit\" name=\"c_delete\" value=\"Delete Course\" />" . 
					 "<input type=\"hidden\" name=\"cid_to_delete\" value=" . $item["CID"] . " /></td>" .
					 "</tr>";
			  }
		  }
		?>
	  </table>
	</div>
  <!-- InstanceEndEditable --></div>
</center>
</body>
<!-- InstanceEnd --></html>
